/*********************************************************************************
 *      Copyright:  (C) 2024 LiYi<1751425323@qq.com>
 *                  All rights reserved.
 *`:wq

 *       Filename:  database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(16/04/24)
 *         Author:  LiYi <1751425323@qq.com>
 *      ChangeLog:  1, Release initial version on "16/04/24 19:31:06"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>

#include "database.h"
#include "logger.h"
#include "socket_pack.h"
#include "sqlite3.h"

#define TABLE_NAME     "PackTable"
struct sqlite3 			*s_clidb = NULL;


int database_init(const char *db_file)
{
	char               sql[256]={0};
	char              *errmsg = NULL;

	if( !db_file )
	{
		log_error("%s() Invalid input arguments\n", __func__);
		return -1;
	}

	if( 0==access(db_file, F_OK) ) //判断文件是否存在，根据返回值可以查询权限
	{
		if( SQLITE_OK != sqlite3_open(db_file, &s_clidb) )
		{
			log_error("open database file '%s' failure\n", db_file);
			return -2;
		}
		log_info("open database file '%s' ok\n", db_file);
		return 0;
	}


	if( SQLITE_OK != sqlite3_open(db_file, &s_clidb) )
	{
		log_error("create database file '%s' failure\n", db_file);
		return -2;
	}

	sqlite3_exec(s_clidb, "pragma synchronous = OFF; ", NULL, NULL, NULL);

	sqlite3_exec(s_clidb, "pragma auto_vacuum = 2 ; ", NULL, NULL, NULL);  //s_clidb--db

	snprintf(sql, sizeof(sql), "CREATE TABLE %s(packet BLOB);", TABLE_NAME); //packet列名，BLOB	列的数据类型blob
	if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, NULL, &errmsg) )
	{
		log_error("create data_table in database file '%s' failure: %s\n", db_file, errmsg);
		sqlite3_free(errmsg); 
		sqlite3_close(s_clidb);  
		unlink(db_file);      
		return -3;
	}

	log_info("create and init database file '%s' ok\n", db_file);
	return 0;
}

void sql_close(void)
{
	log_warn("close sqlite3 database now");
	sqlite3_close(s_clidb);
}

int database_insert(void *buf, int buf_len) 
{
	const char *sql = "INSERT INTO PackTable(packet) VALUES (?);";
	sqlite3_stmt *stmt;
	int rc = sqlite3_prepare_v2(s_clidb, sql, -1, &stmt, NULL);
	if (rc != SQLITE_OK)
	{
		log_error("Failed to prepare insert statement: %s\n", sqlite3_errmsg(s_clidb));
		return -4;
	}

	sqlite3_bind_blob(stmt, 1, buf, buf_len, SQLITE_STATIC);

	rc = sqlite3_step(stmt);
	if (rc != SQLITE_DONE)
	{
		log_error("Failed to execute insert: %s\n", sqlite3_errmsg(s_clidb));
		sqlite3_finalize(stmt);
		return -4;
	}

	sqlite3_finalize(stmt);
	return 0;
}


int database_query_and_clear(int fd)
{
	char sql_query[100];
	int rc;
	sqlite3_stmt *stmt;
	char buf[100];

	sprintf(sql_query, "SELECT * FROM %s", TABLE_NAME);
	rc = sqlite3_prepare_v2(s_clidb, sql_query, -1, &stmt, 0);
	if (rc != SQLITE_OK) 
	{
		log_error("SELECT failure%s\n", sqlite3_errmsg(s_clidb));
		sql_close();
		return -1;
	}

	while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) 
	{
		for (int i = 0; i < sqlite3_column_count(stmt); i++) 
		{
			const char *column_text = (const char *)sqlite3_column_text(stmt, i);
			sprintf(buf, "%s\n", column_text);
			write(fd, buf, strlen(buf));
			sleep(1);
		}
	}

	sprintf(sql_query, "DELETE FROM %s", TABLE_NAME);
	rc = sqlite3_exec(s_clidb, sql_query, 0, 0, NULL);
	if (rc != SQLITE_OK) 
	{
		log_error("DELETE failure: %s\n", sqlite3_errmsg(s_clidb));
		sqlite3_finalize(stmt);
		sql_close();
		return -2;
	}

	sqlite3_finalize(stmt);
	return 0;
}


